package com.siqi.hr.system.organization.dao.mysql;

import com.alibaba.fastjson.JSON;
import com.siqi.hr.common.Constants;
import com.siqi.hr.common.utils.StringUtil;
import com.siqi.hr.common.utils.TableConstant;
import com.siqi.hr.system.common.sql.STable;
import com.siqi.hr.system.common.utils.Common;
import com.siqi.hr.system.organization.dao.intf.DeptDao;
import com.siqi.hr.system.organization.entity.Dept;
import com.siqi.hr.system.organization.entity.EmpReDept;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * 部门信息维护dao实现类层
 * @author     xiaogq
 * Created by xiaogoingqin on 2016/7/21.
 */
@Repository("deptDao")
public class DeptDaoImpl implements DeptDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Dept getById(String tableName,String id) {
        Dept dept = new Dept();
        String sql = "select * from "+tableName+" where id = ? ";
        try {
            dept =jdbcTemplate.queryForObject(sql,new Object[] {id},new Dept.FullRecord());
        }catch (Exception e){
            e.printStackTrace();
        }
        return dept;
    }

    @Override
    public Dept queryRootDept(String tableName)  throws Exception{
        try {
            String sql = "select * from "+tableName+" where pk_t_2_1 is null";
            return jdbcTemplate.queryForObject(sql,new Dept.FullRecord());
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取部门数据
     * @author     xiaogq
     * @param     tableName   表名
     * @return
     */
    @Override
    public List<Dept> queryAllDept(String tableName){
        StringBuffer sql = new StringBuffer("select t2.*, t21.name_varchar as parentName from "+tableName+" as t2 left join "+tableName+" as t21 on t2.pk_t_2_1 = t21.id ");
        sql.append(" where t2.flag_bit = false order by t2.order_num_int asc,t2.id ");
        List<Dept> detpList = new ArrayList<Dept>();
        try{
            detpList = jdbcTemplate.query(sql.toString(),new Dept.FullRecord2());
        }catch (Exception e){
            e.printStackTrace();
        }
        return detpList;
    }

    @Override
    public List<Dept> queryAllChildDeptByRank(String deptTableName, String parentRank) {
        String sql ="select id,name_varchar,dept_rank_varchar from "+deptTableName+" where flag_bit = false ";
        if(StringUtils.isNotBlank(parentRank)){
            sql += " and dept_rank_varchar like '"+parentRank+"%'";
            try {
                return jdbcTemplate.query(sql,new Dept.rootAllDept());
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public long saveAddDept(final String tableName, final Dept dept) throws Exception {
        final StringBuffer sql = new StringBuffer();
        sql.append(" insert into  ").append(tableName).append("(name_varchar,create_date_datetime,pk_t_2_1,dept_rank_varchar,plait_people_decimal,dept_description_textarea,id)");
        sql.append(" values(?,?,?,?,?,?,?) ");
        final Long deptId = jdbcTemplate.queryForObject("select max(id) from "+tableName,Long.class)+1;
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn) {
                PreparedStatement ps = null;
                try {
                    ps = conn.prepareStatement(sql.toString());
                    ps.setString(1, dept.getName());
                    ps.setDate(2,new Date(new java.util.Date().getTime()));
                    ps.setString(3,(dept.getParent() == null || dept.getParent().getId() == null) ? null : dept.getParent().getId().toString());
                    ps.setString(4,dept.getDeptRank());
                    ps.setInt(5,dept.getPlaitPeople() == null ? 0 : dept.getPlaitPeople().intValue());
                    ps.setString(6,dept.getDescription());
                    if(dept.getId() != null){
                        ps.setLong(7,dept.getId());
                    }else {
                        ps.setLong(7,deptId);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return ps;
            }
        });
        if(dept.getId() != null){
            return dept.getId().longValue();
        }else {
            return deptId.longValue();
        }
    }

    @Override
    public void batchAddDept(String deptTableName, final List<Dept> deptList) throws Exception {
        String sql = "insert into "+deptTableName+" (name_varchar,create_date_datetime,pk_t_2_1,dept_rank_varchar,plait_people_decimal,dept_description_textarea,manager_id_list_varchar,id) values(?,?,?,?,?,?,?,?) ";
        final Long deptId = jdbcTemplate.queryForObject("select max(id) from "+deptTableName,Long.class)+1;
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Dept dept = deptList.get(i);
                System.out.println(JSON.toJSONString(dept));
                ps.setString(1, dept.getName());
                ps.setDate(2,new Date(new java.util.Date().getTime()));
                ps.setString(3,(dept.getParent() == null || dept.getParent().getId() == null) ? null : dept.getParent().getId().toString());
                ps.setString(4,dept.getDeptRank());
                ps.setInt(5,dept.getPlaitPeople() == null ? 0 : dept.getPlaitPeople().intValue());
                ps.setString(6,dept.getDescription());
                if(null != dept.getManagerIdList()){
                    ps.setString(7,dept.getManagerIdList().toString());
                }else {
                    ps.setString(7,null);
                }
                if(dept.getId() != null){
                    ps.setLong(8,dept.getId());
                }else {
                    ps.setLong(8,deptId);
                }
            }

            @Override
            public int getBatchSize() {
                return deptList.size();
            }
        });
    }

    @Override
    public void batchUpdateDepts(String deptTableName, final List<Map<String ,Object>> depts) throws Exception {
        String sql = "update "+deptTableName+" set id=?,dept_rank_varchar=?,pk_t_2_1=? where id=?";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Long oldId = (Long)depts.get(i).get("oldId");
                Dept dept = (Dept) depts.get(i).get("dept");
                ps.setLong(1, dept.getId());
                ps.setString(2, dept.getDeptRank());
                ps.setLong(3,dept.getParent().getId());
                ps.setLong(4,oldId);
            }

            @Override
            public int getBatchSize() {
                return depts.size();
            }
        });
    }

    @Override
    public int saveEditDept(String tableName, final Dept dept) {
        StringBuffer sql = new StringBuffer("update "+tableName);
        sql.append(" set name_varchar=?,plait_people_decimal=?,dept_description_textarea=?,manager_id_list_varchar=? where id=?");
        int numbers = jdbcTemplate.update(sql.toString(),new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, dept.getName());
            ps.setInt(2,dept.getPlaitPeople() == null ? 0 : dept.getPlaitPeople().intValue());
            ps.setString(3,dept.getDescription());
            String managerIds = dept.getManagerIdList() == null?"":dept.getManagerIdList().toString();
            ps.setString(4,managerIds);
            ps.setLong(5, dept.getId());
            }
        });
        return numbers;
    }

    @Override
    public void updateDeptRankById(String tableName,long deptId,String deptRank) {
        StringBuffer sql = new StringBuffer("update "+tableName+" set dept_rank_varchar = '"+deptRank);
        sql.append("' where id = '").append(deptId).append("'");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public int updateDeptPidById(String tableName,String ids, String pId,String pdeptRank, String oriDeptRank) {
        String deptRank = pdeptRank + ids + ",";
        String[] sqlList = new String[2];
        StringBuffer sql1 = new StringBuffer("update "+tableName+" set dept_rank_varchar = replace(dept_rank_varchar,'"+ oriDeptRank +"','"+deptRank +"')");
        sql1.append(" where id in (select t.id from (select id from "+tableName+" where dept_rank_varchar like '"+ oriDeptRank +"%') t)");
        sqlList[0] = sql1.toString();

        StringBuilder sql2 = new StringBuilder();
        if(StringUtils.isNotEmpty(pId)){
            sql2.append("update "+tableName+" set pk_t_2_1 = '"+pId+"' where id in ("+ ids +");");
        }
        sqlList[1] = sql2.toString();
        int [] conts = jdbcTemplate.batchUpdate(sqlList);
        return conts.length;
    }

    @Override
    public boolean isRepeatDeptName(Long id,String deptName,String tableName) {
        int counts = 0;
        if(StringUtils.isNotBlank(deptName)){
            StringBuffer sql = new StringBuffer("select count(id) from "+tableName+" where name_varchar = '"+deptName+"'");
            if(id != 0L){
                sql.append(" and id != '").append(id).append("'");
            }
            counts = jdbcTemplate.queryForObject(sql.toString(),Integer.class);
        }
        return counts > 0;
    }

    @Override
    public boolean isChildrenNodes(String tableName,String parentId) {
        StringBuffer sql = new StringBuffer("select count(id) from "+tableName+" where pk_t_2_1 = '");
        sql.append(parentId).append("'");
        int counts = jdbcTemplate.queryForObject(sql.toString(),Integer.class);
        return counts > 0;
    }

    @Override
    public void delDept(String tableName, String id) {
        StringBuffer sql = new StringBuffer("delete from "+tableName +" where id = '"+id+"'");
        jdbcTemplate.execute(sql.toString());
    }

    private String newRankCode(String rank) {
        String newRank = "";
        String newRankPart1 = rank.substring(0, rank.length() - 2);
        String maxRankPart2 = rank.substring(rank.length() - 2);
        Integer newRankPart = Integer.valueOf(Integer.parseInt(maxRankPart2) + 1);
        newRank = newRankPart1;
        if (newRankPart.intValue() < 10) {
            newRank = newRank + "0";
        }
        newRank = newRank + newRankPart.toString();
        return newRank;
    }

    @Override
    public void updateDeptOrders(String tableName, String orderIds) {
        if(StringUtils.isNotBlank(orderIds)){
            List<String> sqls = new ArrayList<String>();
            int i = 0;
            for(String id : orderIds.split(",")){
                i++;
                String sql = "update "+tableName+" set order_num_int = "+ i +" where id = '"+ id +"'";
                sqls.add(sql);
            }
            if (sqls.size() > 0){
                jdbcTemplate.batchUpdate(sqls.toArray(new String[sqls.size()]));
            }
        }
    }

    @Override
    public long countByRelation(String zEmpDetpTableName, String empId, String deptId) {
        String sql = "select count(id) from "+zEmpDetpTableName+" where host_record_id = '"+empId+"' and related_record_id = '"+deptId+"' ";
        try {
            return jdbcTemplate.queryForObject(sql,Long.class);
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public void batchSaveEmpAndDeptRelation(String tableName,final List<Map<String, Object>> zDeptMaps) throws Exception {
        String sql = "insert into "+tableName+"(host_record_id,related_record_id,type) values(?,?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String,Object> zMap = zDeptMaps.get(i);
                ps.setString(1,zMap.get("empId").toString());
                ps.setString(2,zMap.get("deptId").toString());
                ps.setBoolean(3,Boolean.parseBoolean(zMap.get("isMain").toString()));
            }

            @Override
            public int getBatchSize() {
                return zDeptMaps.size();
            }
        });
    }

    @Override
    public List<Map<String, Object>> queryEmpRelateDeptAll(EmpReDept empReDept) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
        StringBuffer sql = new StringBuffer("SELECT b.id,a.id as deptId,a.name_varchar,c.name_varchar pk_t_2_1,a.plait_people_decimal,if(a.flag_bit is true, '停用', '启用') as flag_bit,");
        sql.append("if(b.type is true, '是', '否') as type,b.type as type_flag,a.dept_description_textarea FROM "+empReDept.getDepTableName()+" a LEFT JOIN "+empReDept.getDepTableName()+" c ON a.pk_t_2_1 = c.id  ");
        sql.append(" LEFT JOIN "+empReDept.getzEmpDepTableName()+" b ON a.id = b.related_record_id WHERE b.host_record_id = '").append(empReDept.getEmpId()).append("'");
        if(StringUtils.isNotBlank(empReDept.getSearch())){
            sql.append(" AND (a.name_varchar like '%"+empReDept.getSearch()+"%' or c.name_varchar like '%"+empReDept.getSearch()+"%' ");
            sql.append(" or a.plait_people_decimal like '%"+empReDept.getSearch()+"%' or a.dept_description_textarea like '%"+empReDept.getSearch()+"%'");
            sql.append(")");
        }
        sql.append(" ORDER BY b.id ASC ");
        // 分页
        if(empReDept.getLimit() != 0){
            sql.append(" limit ").append(empReDept.getOffset()+ "," + empReDept.getLimit());
        }
        try {
            resultList = jdbcTemplate.queryForList(sql.toString());
        }catch (Exception e){
            e.printStackTrace();
        }
        return resultList;
    }

    @Override
    public int queryEmpRelateDeptCountAll(EmpReDept empReDept) {
        StringBuffer sql = new StringBuffer("SELECT count(b.id) FROM "+empReDept.getDepTableName()+" a LEFT JOIN "+empReDept.getDepTableName()+" c ON a.pk_t_2_1 = c.id  ");
        sql.append(" LEFT JOIN "+empReDept.getzEmpDepTableName()+" b ON a.id = b.related_record_id WHERE b.host_record_id = '").append(empReDept.getEmpId()).append("'");
        if(StringUtils.isNotBlank(empReDept.getSearch())){
            sql.append(" AND (a.name_varchar like '%"+empReDept.getSearch()+"%' or c.name_varchar like '%"+empReDept.getSearch()+"%' ");
            sql.append(" or a.plait_people_decimal like '%"+empReDept.getSearch()+"%' or a.dept_description_textarea like '%"+empReDept.getSearch()+"%'");
            sql.append(")");
        }
        int total = jdbcTemplate.queryForObject(sql.toString(),Integer.class);
        return  total;
    }

    @Override
    public int delEmpRelateDept(String rId) {
        StringBuffer sql = new StringBuffer("delete from ").append(TableConstant.Z_EMP_DEPT_TNAME).append(" where id = '").append(rId).append("'");
        int counts = jdbcTemplate.update(sql.toString());
        return counts;
    }
}